<?php

class DBManager extends PDO
{

    // =========================================================================
    // 搜索相关
    // =========================================================================

    public function search($keyword, $tableName) {

        $keyword = '%' . $keyword . '%';

        switch ($tableName) {
            case "specificDate":
                $sql = <<<BBB
                    select * from specificDate
                    where content like :keyword 
                    order by date desc
                BBB;
                break;
            case "finished":
                $sql = <<<BBB
                    select * from finished
                    where content like :keyword
                    order by date desc
                BBB;
                break;
            case "yearlyLunar":
                $sql = <<<BBB
                    select * from yearlyLunar
                    where content like :keyword
                    order by monthAndDay asc
                BBB;
                break;
            case "yearly":
                $sql = <<<BBB
                    select * from yearly
                    where content like :keyword 
                    order by (monthAndDay + 0) asc
                BBB;
                break;
            case "monthlyLunar":
                $sql = <<<BBB
                    select * from monthlyLunar
                    where content like :keyword 
                    order by dayOfMonth asc
                BBB;
                break;
            case "monthly":
                $sql = <<<BBB
                    select * from monthly
                    where content like :keyword 
                    order by dayOfMonth asc
                BBB;
                break;
            case "weekly":
                $sql = <<<BBB
                    select * from weekly
                    where content like :keyword 
                    order by dayOfWeek asc
                BBB;
                break;
            case "daily":
                $sql = <<<BBB
                    select * from daily
                    where content like :keyword 
                    order by id asc
                BBB;
                break;
            case "storageBox":
                $sql = <<<BBB
                    select * from storageBox
                    where title like :keyword or content like :keyword
                    order by timeEdit desc
                BBB;
                break;
            case "goals":
                $sql = <<<BBB
                    select * from goals
                    where description like :keyword or
                          actionPlan like :keyword or
                          motivation like :keyword or
                          remark like :keyword or
                          giveupReason like :keyword 
                    order by lastEditTime desc
                BBB;
                break;
            case "summariesAndPlans":
                $sql = <<<BBB
                    select * from summariesAndPlans
                    where title like :keyword or content like :keyword
                    order by timeEdit desc
                BBB;
                break;
            case "shoppingList":
                $sql = <<<BBB
                    select * from shoppingList
                    where title like :keyword or content like :keyword
                    order by timeEdit desc
                BBB;
                break;
        }

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':keyword', $keyword);

        $stmt->execute();
        $rows = $stmt->fetchAll();
        return empty($rows) ? false : $rows;

    }

    // =========================================================================
    // 日程管理
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增，改
    // -------------------------------------------------------------------------

    public function writeToDaily(string $content) {

        $sql = <<<BBB
            replace into daily (id, content) 
            values (1, :content)
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToWeekly(string $content, int $dayOfWeek) {

        $sql = <<<BBB
            replace into weekly (dayOfWeek, content) 
            values (:dayOfWeek, :content);
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':dayOfWeek', $dayOfWeek);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToMonthly(string $content, int $dayOfMonth) {

        $sql = <<<BBB
            replace into monthly (dayOfMonth, content) 
            values (:dayOfMonth, :content);
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':dayOfMonth', $dayOfMonth);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToMonthlyLunar(string $content, int $dayOfMonth) {

        $sql = <<<BBB
            replace into monthlyLunar (dayOfMonth, content) 
            values (:dayOfMonth, :content);
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':dayOfMonth', $dayOfMonth);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToYearly(string $content, string $monthAndDay) {

        $sql = <<<BBB
            replace into yearly (monthAndDay, content) 
            values (:monthAndDay, :content);
        BBB;

        $stmt = $this->prepare($sql);
        // PDO::PARAM_STR 表明要传递的是一个字符串，确保字符串如 0203 不会被转换成整数 203
        $stmt->bindParam(':monthAndDay', $monthAndDay, PDO::PARAM_STR);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToYearlyLunar(string $content, string $monthAndDay) {

        $sql = <<<BBB
            replace into yearlyLunar (monthAndDay, content) 
            values (:monthAndDay, :content);
        BBB;

        $stmt = $this->prepare($sql);
        // PDO::PARAM_STR 表明要传递的是一个字符串，确保字符串如 0203 不会被转换成整数 203
        $stmt->bindParam(':monthAndDay', $monthAndDay, PDO::PARAM_STR);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToSpecificDate(string $content, string $date) {

        $sql = <<<BBB
            replace into specificDate (date, content) 
            values (:date, :content);
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':date', $date);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    public function writeToFinished(string $content, string $date) {

        $sql = <<<BBB
            replace into finished (date, content)
            values (:date, :content);
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':date', $date);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteInSpecificDate($date)
    {

        $sql = <<<BBB
            delete from specificDate 
            where date = :date;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':date', $date);
        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function readFromDaily() {

        $sql = <<<BBB
            select content from daily
            where id = 1
        BBB;

        $result = $this->query($sql);
        return $result->fetchColumn() ?: false;

    }

    // 获取指定星期日的待办
    public function readFromWeekly(int $dayOfWeek) {

        $sql = <<<BBB
            select content from weekly
            where dayOfWeek = :dayOfWeek
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':dayOfWeek', $dayOfWeek);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function readFromMonthly(int $dayOfMonth) {

        $sql = <<<BBB
            select content from monthly
            where dayOfMonth = :dayOfMonth
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':dayOfMonth', $dayOfMonth);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function readFromMonthlyLunar(int $dayOfMonth) {

        $sql = <<<BBB
            select content from monthlyLunar
            where dayOfMonth = :dayOfMonth
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':dayOfMonth', $dayOfMonth);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function readFromYearly(string $monthAndDay) {

        $sql = <<<BBB
            select content from yearly
            where monthAndDay = :monthAndDay
        BBB;

        $stmt = $this->prepare($sql);
        // PDO::PARAM_STR 表明要传递的是一个字符串，确保字符串如 0203 不会被转换成整数 203
        $stmt->bindParam(':monthAndDay', $monthAndDay, PDO::PARAM_STR);
        $stmt->execute();

        return $stmt->fetchColumn() ?: false;

    }

    public function readFromYearlyLunar(string $monthAndDay) {

        $sql = <<<BBB
            select content from yearlyLunar
            where monthAndDay = :monthAndDay
        BBB;

        $stmt = $this->prepare($sql);
        // PDO::PARAM_STR 表明要传递的是一个字符串，确保字符串如 0203 不会被转换成整数 203
        $stmt->bindParam(':monthAndDay', $monthAndDay, PDO::PARAM_STR);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function readFromSpecificDate(string $date) {

        $sql = <<<BBB
            select content from specificDate
            where date = :date
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':date', $date);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function readFromFinished(string $date) {

        $sql = <<<BBB
            select content from finished
            where date = :date
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':date', $date);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function getListOfMonthAndDayLunarWithTask() {

        $sql = <<<BBB
            select monthAndDay from yearlyLunar
            where TRIM(content) != ""
        BBB;

        $arr = [];
        foreach ($this->query($sql) as $row) {
            $arr[] = $row["monthAndDay"];
        }

        return $arr;

    }

    public function getListOfMonthAndDayWithTask()
    {

        $sql = <<<BBB
            select monthAndDay from yearly
            where TRIM(content) != ""
        BBB;

        $arr = [];
        foreach ($this->query($sql) as $row) {
            $arr[] = $row["monthAndDay"];
        }

        return $arr;

    }

    public function getListOfDayOfMonthLunarWithTask() {

        $sql = <<<BBB
            select dayOfMonth from monthlyLunar
            where TRIM(content) != ""
        BBB;

        $arr = [];
        foreach ($this->query($sql) as $row) {
            $arr[] = $row["dayOfMonth"];
        }

        return $arr;

    }

    public function getListOfDayOfMonthWithTask() {

        $sql = <<<BBB
            select dayOfMonth from monthly
            where TRIM(content) != ""
        BBB;

        $arr = [];
        foreach ($this->query($sql) as $row) {
            $arr[] = $row["dayOfMonth"];
        }

        return $arr;

    }

    public function getListOfDayOfWeekWithTask() {

        $sql = <<<BBB
            select dayOfWeek from weekly
            where TRIM(content) != ""
        BBB;

        $arr = [];
        foreach ($this->query($sql) as $row) {
            $arr[] = $row["dayOfWeek"];
        }

        return $arr;

    }

    public function getListOfFutureDateWithTask($today) {

        $sql = <<<BBB
            select date from specificDate
            where (date + 0) > ({$today} + 0) and TRIM(content) != ""
            order by date asc
        BBB;

        $arr = [];
        foreach ($this->query($sql) as $row) {
            $arr[] = $row["date"];
        }

        return $arr;

    }

    // =========================================================================
    // 待办模板
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoTodoTemplate($arr) {

        extract($arr);

        $sql = <<<BBB
            insert into todoTemplate (
                                title,
                                content,
                                sortNumber,
                                timeCreate,
                                timeEdit,
                                timeUse
                            )
                            values (
                                :title,
                                :content,
                                :sortNumber,
                                :timeCreate,
                                :timeEdit,
                                :timeUse
                            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':timeCreate', $timeCreate);
        $stmt->bindParam(':timeEdit', $timeEdit);
        $stmt->bindParam(':timeUse', $timeUse);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteTodoTemplateById($id) {

        $sql = <<<BBB
            delete from todoTemplate
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    public function updateTodoTemplateById($id, $arr) {

        extract($arr);

        $sql = <<<BBB
            update todoTemplate
            set title = :title, 
                content = :content, 
                timeEdit = :timeEdit
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':timeEdit', $timeEdit);
        $stmt->bindParam(":id", $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function getTodoTemplateById($id) {

        $sql = <<<BBB
            select content from todoTemplate
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ?: false;

    }

    public function todoTemplateExist($id) {

        $sql = <<<BBB
            select exists(
                select 1 from todoTemplate 
                where id = :id
            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ? true : false;

    }

    // =========================================================================
    // 购物清单
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoShoppingList($arr) {

        $title = $arr["title"];
        $content = $arr["content"];
        $sortNumber = $arr["sortNumber"];
        $timeCreate = $arr["timeCreate"];
        $timeEdit = $arr["timeEdit"];

        $sql = <<<BBB
            insert into shoppingList (
                                title,
                                content,
                                sortNumber,
                                timeCreate,
                                timeEdit
                            ) 
                            values (
                                :title,
                                :content,
                                :sortNumber,
                                :timeCreate,
                                :timeEdit
                            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':timeCreate', $timeCreate);
        $stmt->bindParam(':timeEdit', $timeEdit);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteShoppingListById($id)
    {

        $sql = <<<BBB
            delete from shoppingList
            where id = :id 
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function deletePurchasedById($id)
    {

        $sql = <<<BBB
            delete from purchased
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    public function updateShoppingListById($id, $arr)
    {

        $title = $arr["title"];
        $content = $arr["content"];
        $timeEdit = $arr["timeEdit"];

        $sql = <<<BBB
            update shoppingList
            set title = :title, content = :content, timeEdit = :timeEdit
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':timeEdit', $timeEdit);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function changeSortNumberOfShoppingList($id, $sortNumber)
    {

        $sql = <<<BBB
            update shoppingList
                set sortNumber = :sortNumber
                where id = :id
        BBB;
        $stmt = $this->prepare($sql);

        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function writeToPurchased(int $id, string $content) {

        $sql = <<<BBB
            replace into purchased (id, content)
            values (:id, :content);
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':content', $content);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function shoppingListExist($id) {

        $sql = <<<BBB
            select exists(
                select 1 from shoppingList
                where id = :id
            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ? true : false;

    }

    public function purchasedExist($id)
    {

        $sql = <<<BBB
            select exists(
                select 1 from purchased
                where id = :id
            )       
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ? true : false;

    }

    public function readFromShoppingListById($id) {

        $sql = <<<BBB
            select * from shoppingList
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $result = $stmt->fetch();
        if (is_array($result)) {

            return [
                "id" => $result["id"],
                "title" => $result["title"],
                "content" => $result["content"],
            ];

        } else {
            return false;
        }

    }

    public function getListOfShoppingLists()
    {

        $sql = <<<BBB
            select id, title, sortNumber from shoppingList
            order by sortNumber asc
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        $data = [];
        while ($row = $stmt->fetch()) {
            $arr = [
                "id" => $row["id"],
                "title" => $row["title"],
                "sortNumber" => $row["sortNumber"],
            ];
            $data[] = $arr;
        }
        return empty($data) ? [] : $data;

    }

    public function readFromPurchased($id) {

        $sql = <<<BBB
            select content from purchased
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        return $stmt->fetchColumn() ?: false;

    }

    // =========================================================================
    // 待办事务收纳箱
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoStorageBox($arr) {

        $title = $arr["title"];
        $content = $arr["content"];
        $sortNumber = $arr["sortNumber"];
        $timeCreate = $arr["timeCreate"];
        $timeEdit = $arr["timeEdit"];

        $sql = <<<BBB
            insert into storageBox (
                                title,
                                content,
                                sortNumber,
                                timeCreate,
                                timeEdit
                            ) 
                            values (
                                :title,
                                :content,
                                :sortNumber,
                                :timeCreate,
                                :timeEdit
                            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':timeCreate', $timeCreate);
        $stmt->bindParam(':timeEdit', $timeEdit);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteStorageBoxById($id)
    {

        $sql = <<<BBB
            delete from storageBox
            where id = :id
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    public function updateStorageBoxById($id, $arr)
    {

        $title = $arr["title"];
        $content = $arr["content"];
        $timeEdit = $arr["timeEdit"];

        $sql = <<<BBB
            update storageBox
            set title = :title, content = :content, timeEdit = :timeEdit
            where id = :id
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':timeEdit', $timeEdit);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function changeSortNumberOfStorageBox($id, $sortNumber)
    {

        $sql = <<<BBB
            update storageBox
                set sortNumber = :sortNumber
                where id = :id
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function storageBoxExist($id)
    {

        $sql = <<<BBB
             select exists(
                select 1 from storageBox
                where id = :id
            )       
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ? true : false;

    }

    public function getListOfStorageBoxs()
    {

        $sql = <<<BBB
            select id, title, sortNumber from storageBox
            order by sortNumber asc
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        $data = [];
        while ($row = $stmt->fetch()) {
            $arr = [
                "id" => $row["id"],
                "title" => $row["title"],
                "sortNumber" => $row["sortNumber"],
            ];
            $data[] = $arr;
        }
        return $data;

    }

    // 读取待办事务收纳箱中的内容
    public function readFromStorageBoxById($id) {

        $sql = <<<BBB
            select * from storageBox
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $row = $stmt->fetch();

        if (is_array($row)) {
            return [
                "id" => $row["id"],
                "title" => $row["title"],
                "content" => $row["content"],
            ];
        } else {
            return false;
        }

    }

    // =========================================================================
    // 目标管理
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    // 新建目标时插入数据
    public function insertIntoGoals(array $data) {

        $sortNumber   = $data["sortNumber"]; // 排序号
        $description  = $data["description"]; // 目标描述
        $actionPlan   = $data["actionPlan"]; // 行动计划
        $motivation   = $data["motivation"]; // 行动动力
        $startDate    = $data["startDate"]; // 原定开始日期
        $endDate      = $data["endDate"]; // 原定结束日期
        $createTime   = $data["createTime"]; // 创建时间
        $lastEditTime = $data["lastEditTime"]; // 最后修改时间

        $status       = $data["status"];
        $achieveDate  = $data["achieveDate"]; // 目标实现日期
        $giveupDate   = $data["giveupDate"]; // 目标放弃日期
        $giveupReason = $data["giveupReason"]; // 目标放弃原因

        $sql = <<<BBB
            INSERT INTO goals (
                                  sortNumber,
                                  description,
                                  actionPlan,
                                  motivation,
                                  createTime,
                                  lastEditTime,
                                  startDate,
                                  endDate,

                                  status,
                                  achieveDate,
                                  giveupDate,
                                  giveupReason
                              )
                       VALUES (
                                  :sortNumber,
                                  :description,
                                  :actionPlan,
                                  :motivation,
                                  :createTime,
                                  :lastEditTime,
                                  :startDate,
                                  :endDate,

                                  :status,
                                  :achieveDate,
                                  :giveupDate,
                                  :giveupReason
                              );
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':actionPlan', $actionPlan);
        $stmt->bindParam(':motivation', $motivation);
        $stmt->bindParam(':createTime', $createTime);
        $stmt->bindParam(':lastEditTime', $lastEditTime);
        $stmt->bindParam(':startDate', $startDate);
        $stmt->bindParam(':endDate', $endDate);
        $stmt->bindParam(':status', $status);
        $stmt->bindParam(':achieveDate', $achieveDate);
        $stmt->bindParam(':giveupDate', $giveupDate);
        $stmt->bindParam(':giveupReason', $giveupReason);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteGoal($id) {

        $sql = <<<BBB
            delete from goals
            where id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    // 更改目标的 sortNumber
    public function changeSortNumberOfGoal($goalId, $sortNumber) {

        $sql = <<<BBB
            update goals 
            set sortNumber = :sortNumber
            where id = :goalId
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':goalId', $goalId);

        return $stmt->execute();

    }

    // 编辑目标时更新数据
    public function updateIntoGoals(array $data) {

        // 这两项的值，在编辑窗口中不存在
        // $sortNumber   = $data["sortNumber"]; // 排序号
        // $createTime = $data["createTime"]; // 创建时间

        $id           = $data["id"];
        $description  = $data["description"]; // 目标描述
        $actionPlan   = $data["actionPlan"]; // 行动计划
        $motivation   = $data["motivation"]; // 行动动力
        $status       = $data["status"]; // 完成状态
        $remark       = $data["remark"]; // 备注信息
        $startDate    = $data["startDate"]; // 原定开始日期
        $endDate      = $data["endDate"]; // 原定结束日期
        $achieveDate  = $data["achieveDate"]; // 目标实现日期
        $giveupDate   = $data["giveupDate"]; // 目标放弃日期
        $giveupReason = $data["giveupReason"]; // 目标放弃原因
        $lastEditTime = $data["lastEditTime"]; // 最后编辑时间

        $sql = <<<BBB
            update goals
                set description  = :description,
                    actionPlan   = :actionPlan,
                    motivation   = :motivation,
                    status       = :status,
                    remark       = :remark,
                    lastEditTime = :lastEditTime,
                    startDate    = :startDate,
                    endDate      = :endDate,
                    achieveDate  = :achieveDate,
                    giveupDate   = :giveupDate,
                    giveupReason = :giveupReason
                where id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':actionPlan', $actionPlan);
        $stmt->bindParam(':motivation', $motivation);
        $stmt->bindParam(':status', $status);
        $stmt->bindParam(':remark', $remark);
        $stmt->bindParam(':lastEditTime', $lastEditTime);
        $stmt->bindParam(':startDate', $startDate);
        $stmt->bindParam(':endDate', $endDate);
        $stmt->bindParam(':achieveDate', $achieveDate);
        $stmt->bindParam(':giveupDate', $giveupDate);
        $stmt->bindParam(':giveupReason', $giveupReason);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function goalExist($id) {

        $sql = <<<BBB
             select exists(
                select 1 from goals
                where id = :id
            )       
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ? true : false;

    }

    /**
     * 根据指定的 id，返回相应目标的全部信息，如目标描述，行动计划等。
     */
    public function getGoalInfor($id) {

        $sql = <<<BBB
            select * from goals
            where id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        return $stmt->fetch();

    }

    /**
     * 根据传入的筛选条件，返回一个列表，该列表中包含所有满足这些筛选条件的目标的 id 和 description。
     * @param string $class 目标分类。all 全部，short 短期，medium 中期，long 长期。
     * @param string $status 目标状态。all 全部，ongoing 进行中，waiting 未进行，achieved 已实现，abandoned 已放弃。
     * @param string $sort 排序条件。sortNumber 排序号，createTime 创建时间，startDate 开始日期，endDate 结束日期，achieveDate 实现日期，giveupDate 放弃日期。
     * @param string $sequence 顺序。asc 正序，从小到大。 desc倒序，从大到小。
     * @return array
     */
    public function getListOfGoals($class, $status, $sort, $sequence) {

        /* 根据传入的不同条件，构建不同的 sql 语句。 */

        /* sql 语句构建，第一步 */

        $classStr = "";
        $statusStr = "";
        $sortStr = "";
        $sequenceStr = "";

        switch ($class) {
            case "all":
                // pass
                break;
            case "short":
                // 短期，三月以内
                // 这里的 sql 语句要非常注意，字段名一定要用双引号包括，用单引号的话会出错。
                $classStr = <<<BBB
                    julianday("endDate") - julianday("startDate") < 90
                BBB;
                break;
            case "medium":
                // 中期，三年以内
                // 这里的 sql 语句要非常注意，字段名一定要用双引号包括，用单引号的话会出错。
                $classStr = <<<BBB
                    julianday("endDate") - julianday("startDate") > 89 and julianday("endDate") - julianday("startDate") <= (365*3)
                BBB;
                break;
            case "long":
                // 长期，三年以上
                // 这里的 sql 语句要非常注意，字段名一定要用双引号包括，用单引号的话会出错。
                $classStr = <<<BBB
                    julianday("endDate") - julianday("startDate") > (365*3)
                BBB;
                break;
        }

        switch ($status) {
            case "all":
                // pass
                break;
            case "ongoing":
                // 进行中
                $statusStr = "status = 'ongoing'";
                break;
            case "overdue":
                // 已逾期
                $statusStr = <<<BBB
                    status = 'ongoing' and endDate < date('now', 'localtime')
                BBB;
                break;
            case "waiting":
                // 未进行
                $statusStr = "status = 'waiting'";
                break;
            case "achieved":
                // 已实现
                $statusStr = "status = 'achieved'";
                break;
            case "abandoned":
                // 已放弃
                $statusStr = "status = 'abandoned'";
                break;
        }

        switch ($sort) {
            case "sortNumber":
                // 排序号
                $sortStr = "order by sortNumber";
                break;
            case "createTime":
                // 创建时间
                $sortStr = "order by createTime";
                break;
            case "lastEditTime":
                $sortStr = "order by lastEditTime";
                break;
            case "startDate":
                // 原定开始日期
                $sortStr = "order by startDate";
                break;
            case "endDate":
                // 原定结束日期
                $sortStr = "order by endDate";
                break;
            case "achieveDate":
                // 实际实现日期
                $sortStr = "order by achieveDate";
                break;
            case "giveupDate":
                // 放弃日期
                $sortStr = "order by giveupDate";
                break;
        }

        switch ($sequence) {
            case "asc":
                // 升序 ，从小到大
                $sequenceStr = "asc;";
                break;
            case "desc":
                // 降序，从大到小
                $sequenceStr = "desc;";
                break;
        }

        // ---------------------------------------------------------------------

        /* sql 语句构建，第二步 */

        $sql = <<<BBB
            select id, 
                   description, 
                   sortNumber, 
                   createTime, 
                   lastEditTime, 
                   achieveDate, 
                   giveupDate, 
                   startDate, 
                   endDate from goals
        BBB;

        if ($classStr == "") {
            if ($statusStr == "") {
                // pass
            } else {
                $sql .= " where " . $statusStr;
            }
        } else {
            if ($statusStr == "") {
                $sql .= " where " . $classStr;
            } else {
                $sql .= " where " . $classStr . " and " . $statusStr;
            }
        }

        $sql .= " " . $sortStr . " " . $sequenceStr;

        // ---------------------------------------------------------------------

        /* sql 语句构建完毕，开始执行 sql 语句 */

        $goals = [];
        foreach ($this->query($sql) as $row) {

            $goal = [];
            $goal["id"] = $row["id"];
            $goal["description"] = $row["description"];

            $goal["sortNumber"] = $row["sortNumber"];
            $goal["createTime"] = $row["createTime"];
            $goal["lastEditTime"] = $row["lastEditTime"];
            $goal["startDate"] = $row["startDate"];
            $goal["endDate"] = $row["endDate"];
            $goal["achieveDate"] = $row["achieveDate"];
            $goal["giveupDate"] = $row["giveupDate"];

            $goals[] = $goal;

        }

        return $goals;

    }


    // =========================================================================
    // 总结与规划
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoSummariesAndPlans($arr)
    {

        $title = $arr["title"];
        $content = $arr["content"];
        $year = $arr["year"];
        $sortNumber = $arr["sortNumber"];
        $timeCreate = $arr["timeCreate"];
        $timeEdit = $arr["timeEdit"];

        $sql = <<<BBB
            insert into summariesAndPlans (
                                title,
                                content,
                                year,
                                sortNumber,
                                timeCreate,
                                timeEdit
                            ) 
                            values (
                                :title,
                                :content,
                                :year,
                                :sortNumber,
                                :timeCreate,
                                :timeEdit
                            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':year', $year);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':timeCreate', $timeCreate);
        $stmt->bindParam(':timeEdit', $timeEdit);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteSummariesAndPlansById($id)
    {

        $sql = <<<BBB
            delete from summariesAndPlans
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    public function updateSummariesAndPlans($id, $arr)
    {

        $title = $arr["title"];
        $year = $arr["year"];
        $content = $arr["content"];
        $timeEdit = $arr["timeEdit"];

        $sql = <<<BBB
            update summariesAndPlans 
            set title = :title, year = :year, content = :content, timeEdit = :timeEdit
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':year', $year);
        $stmt->bindParam(':timeEdit', $timeEdit);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function changeSortNumberOfSummariesAndPlans($id, $sortNumber)
    {

        $sql = <<<BBB
            update summariesAndPlans
                set sortNumber = :sortNumber
                where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function summaryAndPlanExist($id)
    {
        $sql = <<<BBB
            select exists(
                select 1 from summariesAndPlans
                where id = :id
            )
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);

        $stmt->execute();
        return $stmt->fetchColumn() ? true : false;

    }

    public function readFromSummariesAndPlansById($id)
    {

        $sql = <<<BBB
            select * from summariesAndPlans
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        // 成功返回数组，失败返回 false
        return $stmt->fetch();

    }

    public function getListOfYearsOfSummariesAndPlans()
    {
        $sql = <<<BBB
            select year from summariesAndPlans
            order by year desc
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        $years = [];

        while ($row = $stmt->fetch()) {
            // 数组去重
            if (!in_array($row["year"], $years)) {
                $years[] = $row["year"];
            }
        }

        return empty($years) ? false : $years;

    }

    public function getListOfSummariesAndPlans($year, $sort, $sequence)
    {
        $yearStr = "";
        $sortStr = "";
        $sequenceStr = "";

        switch ($year) {
            case "all":
                $yearStr = "";
                break;
            default:
                $yearStr = <<<BBB
                    where year = "$year" 
                BBB;
        }

        switch ($sort) {
            case "sortNumber":
                $sortStr = <<<BBB
                    order by sortNumber 
                BBB;
                break;
            case "timeEdit":
                $sortStr = <<<BBB
                    order by timeEdit 
                BBB;
                break;
        }

        switch ($sequence) {
            case "asc":
                $sequenceStr = "asc";
                break;
            case "desc":
                $sequenceStr = "desc";
                break;
        }

        $sql = <<<BBB
            select id, title, year, sortNumber from summariesAndPlans
            $yearStr
            $sortStr $sequenceStr
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        $data = [];
        while ($row = $stmt->fetch()) {
            $arr = [
                "id" => $row["id"],
                "title" => $row["title"],
                "year" => $row["year"],
                "sortNumber" => $row["sortNumber"],
            ];
            $data[] = $arr;
        }
        return $data;

    }


    // =========================================================================
    // 创建表
    // =========================================================================

    public function createTables() {

        $sqlCreateDaily = <<<BBB
            CREATE TABLE daily (
                id      INTEGER PRIMARY KEY
                                NOT NULL,
                content TEXT
            );
        BBB;

        $sqlCreateWeekly = <<<BBB
            CREATE TABLE weekly (
                dayOfWeek INTEGER PRIMARY KEY
                                  UNIQUE
                                  NOT NULL,
                content   TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateMonthly = <<<BBB
            CREATE TABLE monthly (
                dayOfMonth INTEGER PRIMARY KEY
                                   UNIQUE
                                   NOT NULL,
                content    TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateMonthlyLunar = <<<BBB
            CREATE TABLE monthlyLunar (
                dayOfMonth INTEGER PRIMARY KEY
                                   UNIQUE
                                   NOT NULL,
                content    TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateYearly = <<<BBB
            CREATE TABLE yearly (
                monthAndDay TEXT PRIMARY KEY
                                 UNIQUE
                                 NOT NULL,
                content     TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateYearlyLunar = <<<BBB
            CREATE TABLE yearlyLunar (
                monthAndDay TEXT PRIMARY KEY
                                 UNIQUE
                                 NOT NULL,
                content     TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateSpecificDate = <<<BBB
            CREATE TABLE specificDate (
                date    TEXT PRIMARY KEY
                             UNIQUE
                             NOT NULL,
                content TEXT
            )
            WITHOUT ROWID;
        BBB;

        $sqlCreateSummariesAndPlans = <<<BBB
            CREATE TABLE summariesAndPlans (
                id         INTEGER PRIMARY KEY
                                   UNIQUE,
                title      TEXT    NOT NULL,
                content    TEXT,
                year       TEXT    NOT NULL,
                sortNumber TEXT    NOT NULL
                                   UNIQUE,
                timeCreate TEXT,
                timeEdit   TEXT,
                timeRead   TEXT
            );
        BBB;

        $sqlCreateStorageBox = <<<BBB
            CREATE TABLE storageBox (
                id         INTEGER PRIMARY KEY
                                   UNIQUE,
                title      TEXT    NOT NULL,
                content    TEXT,
                sortNumber TEXT    NOT NULL
                                   UNIQUE,
                timeCreate TEXT,
                timeEdit   TEXT,
                timeRead   TEXT
            );
        BBB;

        $sqlCreateGoals = <<<BBB
            CREATE TABLE goals (
                id           INTEGER PRIMARY KEY
                                     UNIQUE,
                sortNumber   INTEGER UNIQUE,
                description  TEXT    NOT NULL,
                actionPlan   TEXT,
                motivation   TEXT,
                status       TEXT,
                remark       TEXT,
                createTime   TEXT    NOT NULL,
                lastEditTime TEXT    NOT NULL,
                startDate    TEXT    NOT NULL,
                endDate      TEXT    NOT NULL,
                achieveDate  TEXT,
                giveupDate   TEXT,
                giveupReason TEXT
            );
        BBB;

        $sqlCreateShoppingList = <<<BBB

            CREATE TABLE shoppingList (
                id         INTEGER PRIMARY KEY
                                   UNIQUE,
                title      TEXT    NOT NULL,
                content    TEXT,
                sortNumber TEXT    NOT NULL
                                   UNIQUE,
                timeCreate TEXT,
                timeEdit   TEXT,
                timeRead   TEXT
            );

        BBB;

        $sqlCreateFinished = <<<BBB
            CREATE TABLE finished (
                date    TEXT UNIQUE
                             NOT NULL
                             PRIMARY KEY,
                content TEXT
            );
        BBB;

        $sqlCreatePurchased = <<<BBB
            CREATE TABLE purchased (
                id      INTEGER PRIMARY KEY
                                UNIQUE
                                NOT NULL,
                content TEXT
            );
        BBB;

        $sqlCreateTodoTemplate = <<<BBB
            CREATE TABLE todoTemplate (
                id         INTEGER PRIMARY KEY
                                UNIQUE,
                title      TEXT,
                content    TEXT,
                sortNumber TEXT UNIQUE
                                NOT NULL,
                timeCreate TEXT    NOT NULL,
                timeEdit   TEXT,
                timeUse    TEXT
            );
        BBB;

        $this->exec($sqlCreateDaily);
        $this->exec($sqlCreateWeekly);
        $this->exec($sqlCreateMonthly);
        $this->exec($sqlCreateMonthlyLunar);
        $this->exec($sqlCreateYearly);
        $this->exec($sqlCreateYearlyLunar);
        $this->exec($sqlCreateSpecificDate);
        $this->exec($sqlCreateFinished);
        $this->exec($sqlCreateStorageBox);
        $this->exec($sqlCreateSummariesAndPlans);
        $this->exec($sqlCreateGoals);
        $this->exec($sqlCreateShoppingList);
        $this->exec($sqlCreatePurchased);
        $this->exec($sqlCreateTodoTemplate);

    }

    // =========================================================================
    // 删除表
    // =========================================================================

    public function deleteTables() {

        $sqlDeleteShoppingList = <<<BBB
            DROP TABLE IF EXISTS shoppingList;
        BBB;

        $sqlDeleteGoals = <<<BBB
            DROP TABLE IF EXISTS goals;
        BBB;

        $sqlDeleteSummariesAndPlans = <<<BBB
            DROP TABLE IF EXISTS summariesAndPlans;
        BBB;

        $sqlDeleteStorageBox = <<<BBB
            DROP TABLE IF EXISTS storageBox;
        BBB;

        $sqlDeleteDaily = <<<BBB
            DROP TABLE IF EXISTS daily;
        BBB;

        $sqlDeleteWeekly = <<<BBB
            DROP TABLE IF EXISTS weekly;
        BBB;

        $sqlDeleteMonthly = <<<BBB
            DROP TABLE IF EXISTS monthly;
        BBB;

        $sqlDeleteMonthlyLunar = <<<BBB
            DROP TABLE IF EXISTS monthlyLunar;
        BBB;

        $sqlDeleteYearly = <<<BBB
            DROP TABLE IF EXISTS yearly;
        BBB;

        $sqlDeleteYearlyLunar = <<<BBB
            DROP TABLE IF EXISTS yearlyLunar;
        BBB;

        $sqlDeleteSpecificDate = <<<BBB
            DROP TABLE IF EXISTS specificDate;
        BBB;

        $sqlDeleteFinished = <<<BBB
            DROP TABLE IF EXISTS finished;
        BBB;

        $sqlDeletePurchased = <<<BBB
            DROP TABLE IF EXISTS purchased;
        BBB;

        $sqlDeleteTodoTemplate = <<<BBB
            DROP TABLE IF EXISTS todoTemplate;
        BBB;

        $this->exec($sqlDeleteDaily);
        $this->exec($sqlDeleteWeekly);
        $this->exec($sqlDeleteMonthly);
        $this->exec($sqlDeleteMonthlyLunar);
        $this->exec($sqlDeleteYearly);
        $this->exec($sqlDeleteYearlyLunar);
        $this->exec($sqlDeleteSpecificDate);
        $this->exec($sqlDeleteFinished);
        $this->exec($sqlDeleteStorageBox);
        $this->exec($sqlDeleteSummariesAndPlans);
        $this->exec($sqlDeleteGoals);
        $this->exec($sqlDeleteShoppingList);
        $this->exec($sqlDeletePurchased);
        $this->exec($sqlDeleteTodoTemplate);

    }

    // =========================================================================
    // 重置数据库
    // =========================================================================

    public function recreateTables() {
        $this->deleteTables();
        $this->createTables();
    }

}
